Restricting and sorting | Banking Account Management System in SQL

Note: All SQL queries used in the Case study is tested on Oracle,SQLServer,MySQL and PostgreSQL.

1. Retrieve all transactions where the amount is greater than 1000.
SELECT *
FROM Transactions
WHERE Amount > 1000;
Explanation
The SQL query gets all columns from the transaction table for records where the amount is greater than 1000. The SELECT * command indicates that all columns from the transaction table will be included in the output. The WHERE clause filters the results ensuring that only transactions greater than 1000 are selected. This query is useful for identifying large transactions that may be important for financial analysis, fraud detection or audit purposes. By isolating transactions that exceed a certain threshold, the query helps focus on entries that are potentially high risk or high value.

2. List all transactions sorted by date in descending order.
SELECT *
FROM Transactions
ORDER BY tDate DESC;
Explanation
The query read all the records from the Transactions table and sorts them in descending order by the date column. This means that the most recent transactions will appear first in the result set and the oldest transactions will be listed later. The ORDER BY clause is used to sort the data in a specific order and the DESC keyword stipulates that the sorting should be in descending order from oldest to newest. This query is useful if you want to view your most recent transactions or determine recent account activity. The * symbol means that all columns of the Transactions table will be included in the output.

3. Retrieve all deposits and sort them by amount in descending order.
SELECT *
FROM Transactions
WHERE TransactionType = 'Deposit'
ORDER BY Amount DESC;
Explanation
The query is designed to retrieve all transactions from the transactions table where the transaction type is 'Deposit'. Filters the data to include only records whose Transaction Type column contains the value "Deposit". After filtering it then sorts those deposit transactions in order of the amount column meaning the largest deposit amount will be displayed first followed by smaller deposits. The ORDER BY amount DESC guarantees that the results are ordered from highest to lowest deposit amount. The * symbol shows that all columns in the transaction table will be involved in the results. This query is particularly useful when we want to see all deposit transactions and focus on large amounts.

4. Retrieve the 5 most recent transactions.
Oracle
SELECT * FROM
(SELECT * FROM Transactions ORDER BY tDate DESC)
WHERE ROWNUM <= 5;
Explanation
In this SQL query the inner SELECT statement sorts the data in the Transactions table in descending order based on the date column. The outer SELECT statement then filters the data sorted by the ROWNUM condition to include only the first five rows. This means that the query returns the most five recent transactions from the transaction table.
The ROWNUM keyword states the number of rows returned in the result set. Basically this query allows you to retrieve a specific subset of data in this case the last 5 records based on a precise order. This is a common way to retrieve a limited number of records from a table that meet certain criteria such as retrieving the most recent transactions for analysis or reporting purposes.
MySQL and PostgreSQL
SELECT * FROM
(SELECT * FROM Transactions ORDER BY tDate DESC) AS sorted_table LIMIT 5;
This query retrieves all the columns from the transaction table and sorts them based on the date column. Sorted data is stored as a temporary subquery called sorted_table. The LIMIT 5 statement limits output to only the first five rows of sorted data. This allows the most recent transactions to be entered into the table in a concise and efficient manner. Queries help to quickly identify the most recent records without having to manually examine the entire data set.
Generally, this query effectively retrieves and returns the 5 most recent transactions from the transaction table which demonstrates the flexibility and power of SQL queries in data management and manipulation.
SQL Server
SELECT TOP 5 *
FROM Transactions
ORDER BY tDate DESC;
Explanation
This query selects the first five rows of the transactions table and sorts them based on the date column. It helps to quickly see the last or most recent transaction in the table without searching all the data. By limiting the result to only the first five rows users can easily recognize the most recent transactions and focus on the most up-to-date information. This query is useful for competently retrieving and displaying the most current data from a table.

5. Sort transactions first by transaction type and then by amount within each type.
Oracle , MySQL, SQLServer ,PostgreSQL
SELECT *
FROM Transactions
ORDER BY TransactionType ASC, Amount DESC;
Explanation
This query fetches all columns from the transactions table and arranges the data in ascending order based on transaction type and then in descending order based on amount. Through this arrangement, the query will first organize the transactions alphabetically and then sort them within each category by amount. This enables a well-structured display of transactions, facilitating the analysis and comparison of different transaction types based on their level. Querying offers a comprehensive method for organizing and presenting transaction data for further analysis or reporting purposes.

Restricting and sorting data for Account Management and Transaction Insights Case Study

6. Retrieve transactions greater than 1000 that are either deposits or withdrawals.
SELECT *
FROM Transactions
WHERE Amount > 1000

AND (TransactionType = 'Withdrawal' OR TransactionType = 'Deposit');
Explanation
This query reads all rows from the transaction table where the amount value is more than 1000 and the transaction type is withdrawal or deposit. By specifying these conditions the query checks transactions that do not meet the condition that the amount is greater than 1000 and is either a withdraw or a deposit.
This allows for separation into micro-transaction types that involve withdrawing or depositing large amounts of money.

7. Retrieve transactions that occurred within the first half of 2023.
Oracle
SELECT * FROM Transactions
WHERE
tDate BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND
TO_DATE('2023-06-30', 'YYYY-MM-DD');
To display all columns from the Transactions table for transactions dated between 1 January 2023, and 30 June 2023 the query uses the TO_DATE function to convert the date strings into the appropriate format for comparison. The use of the BETWEEN operator ensures that only transactions falling within the specified date range are included. Users can then focus on transactions occurring in the first half of 2023.
MySQL SQLServer PostgeSQL
SELECT *
FROM Transactions
WHERE tDate BETWEEN '2023-01-01' AND '2023-06-30';
Explanation
This query selects all columns from the Transactions table where the Date is between January 1, 2023, and June 30, 2023. It filters and retrieves the data pertaining to a specific period in time, so users can draw attention to transactions within a period. The BETWEEN operator will narrow down results, returning only transactions made between these two dates. This query is very good at analyzing/extracting data for a time range within a table called Transactions.

8. Retrieve transactions that are either deposits or withdrawals.
Query is same for all databases.
SELECT *
FROM Transactions
WHERE TransactionType IN ('Deposit', 'Withdrawal');
Explanation
this query will read all records and columns from the transaction table where the type of transaction is either Withdrawal or Depossit. The query filters the rows or data from transaction table to include only the rows that has specific transaction types.
thats why it will return the transaction categorized under withdrawal and deposit. It will filter to find and display specific types of transactions in a specific data set.

9. List all customers who do not have an address listed.
Query is same for all databases.
SELECT *
FROM Customers
WHERE Address IS NULL;
Explanation
The query will select all information about customers from the database table named Customers, where the column Address is empty or doesn't have a value; that is to say, NULL. It aids in finding out customers with no record of their address in the database. Here, the * asterisk symbol means that all columns of customer data shall be included in the result set. This is a simple query that filters the retrieval of some customer records, which have not kept their information regarding their address. It is a very helpful tool in managing and organizing customer data.

10. Sort customers by address in ascending order placing NULL values last
Query in Oracle and PostgreSQL
SELECT *
FROM Customers
ORDER BY Address ASC NULLS LAST;
Explanation
This would be a query that selects all customer information from a database table named Customer and sorts the results in ascending order on the address column. The NULLS last part ensures that customers who have no address are listed at the very end of the sorted list. This way the customer data will be sorted by address, with missing addresses appearing furthest down the sorted list.
Query in MySQL and SQLServer
SELECT * FROM Customers ORDER BY Address ASC;
Explanation
This is a query that retrieves all customer details from the Customers table, and sorts the results in ascending order based on the address column. It sorts customer addresses from A to Z. This will help you view the list of customers sorted by their addresses.

11. Sort customers by address in ascending order, placing NULL values first
Query in Oracle and SQLServer
SELECT * FROM Customers ORDER BY (CASE WHEN Address IS NULL THEN 1 ELSE 0 END), Address ASC;
Explanation
This will return all customer information from the table of customers sorted according to whether or not the address column is empty or NULL.
First of all, customers are sorted according to those with empty address or NULL address and then customers with addresses are sorted in an ascending manner.
It then sorts the customer data in such a way that all items without addresses are on top, while those with addresses are at the bottom, arranged in alphabetical order by address. It, therefore, helps in the presentation of all customers accurately hence easy identification and management of customers whose addresses are missing.
MySQL and PostgreSQL
SELECT *
FROM Customers
ORDER BY Address IS NULL, Address ASC;
Explanation
This query selects all customer information in the Customers table, ordered by whether column Address is NULL or not. All customers with a NULL address would be put at the beginning of the list, followed by those that have an address, in ascending order. Those with NULL addresses would be the first and those having addresses would be in alphabetical order. This will enable the information of the customer to have a structured display so that customers without an address can easily be spotted and managed.

12. Retrieve the top 5 most recent transactions using the FETCH FIRST clause.
Oracle
SELECT *
FROM Transactions
ORDER BY Date DESC
FETCH FIRST 5 ROWS ONLY;
Explanation
This query will return all columns from the Transactions table. The result is then sorted descending on the values of the Date column. The "FETCH FIRST 5 ROWS ONLY" statement restricts the output to return only the first five rows of sorted data, thus ensuring that only the most recent transactions are returned.
MySQL and PostgeSQL
SELECT * FROM Transactions
ORDER BY Date DESC LIMIT 5;
Explanation
This query returns all columns from the transaction table but the date column in descending order. The LIMIT 5 statement limits the output to the first 5 rows of datas so one can now very briefly see the latest transactions made in the database. This helps to quickly access the 5 most recent transactions.
SQLServer
SELECT TOP 5 * FROM Transactions
ORDER BY Date DESC;
Explanation
This query will select the top 5 rows of all columns from the Transactions table and sort them in descending order according to the Date column values. The TOP 5 statement limits output to only the first five rows of sorted data, thus providing a comprehensive view of the most recent transactions in the database.

13. Retrieve all customers whose names start with 'J'.
The Query is same for all databases.
SELECT *
FROM Customers
WHERE Name LIKE 'J%';
Explanation
This is a query designed to return every record in the Customers table whose names all begin with the letter "J". For this type of search, a special pattern 'J%' will be used to find and display only those rows that match the condition. Using this search criterion all rows containing details of customers whose names start with letter ‘J’ is displayed.

14. Perform a case-insensitive search for customers whose names start with 'j'
SELECT * FROM customers
WHERE LOWER(name) LIKE 'j%';
Explanation
This query selects all columns from the "customers" table where the name, after being converted to lowercase using the LOWER function, starts with the letter 'j'. The LIKE operator will then take part in pattern matching; here, '%' stands for any sequence of characters. Thus, the query will return all rows from the "customers" table where the name starts with 'j', regardless of case.

15. Retrieve all customers whose names do not end with 'Smith'.
Query is same for all databases
SELECT *
FROM Customers
WHERE Name NOT LIKE '%Smith';
Explanation
This will be a query that will select all columns in the 'Customers' table where the 'Name' column does not contain the string 'Smith' anywhere. This means that the NOT LIKE '%Smith' condition only eliminates all rows whose name contains the name "Smith" anywhere.

16. Retrieve deposits over 1000 or withdrawals over 500.
SELECT *
FROM Transactions
WHERE (TransactionType = 'Deposit' AND Amount > 1000)
OR (TransactionType = 'Withdrawal' AND Amount > 500);
Explanation
This query will return all the information from the transaction table with the transaction type either deposit when the amount is greater than 1000 or withdrawal when the amount is greater than 500. This is very useful for filtering and returning specific transactions according to their type and amount criteria.

17. Retrieve transactions that are not deposits or withdrawals.
SELECT *
FROM Transactions
WHERE TransactionType NOT IN ('Deposit', 'Withdrawal');
Explanation
This SQL query selects all columns from the Transactions table where the transaction type is neither Deposit nor Withdrawal. The NOT IN operator will eliminate rows containing values specified in the Transaction Type column. The purpose of this query is to filter out all transactions that do not fall into the category of deposit or withdrawal so that other types of transactions can be further examined on a dataset.

18. Sort transactions by a calculated column.
SELECT *, Amount * 1.05 AS AdjustedAmount
FROM Transactions
ORDER BY AdjustedAmount DESC;
Explanation
This is an SQL query which selects all columns from the Transactions table adds new column for AdjustedAmount calculated by multiplying the column Amount with factor of 1.05 and then sorts the results in descending order based on the AdjustedAmount column.

19. Retrieve customers whose names contain the letter 'a'.
SELECT *
FROM Customers
WHERE Name LIKE '%a%';
Explanation
This query finds customers whose names contain the letter ‘a’ in any part of the name. The LIKE operator %a% matches any string of characters that contains character ‘a’.

20.Retrieve the total number of transactions and sort by the highest amount.
SELECT TransactionType,
COUNT(*) AS TotalTransactions
FROM Transactions
GROUP BY TransactionType
ORDER BY COUNT(*) DESC;
Explanation
This will select TransactionType and count how many transactions there are of each type. It groups the results by TransactionType and arranges them in descending order with respect to the number of transactions of each type.

21. Retrieve customers whose names do not contain the letter 'e'.
SELECT *
FROM Customers
WHERE Name NOT LIKE '%e%';
Explanation
This query selects all columns from the Customers table where the column name does not contain the letter ‘e’ anywhere. The NOT LIKE operator filters out rows whose column names do not contain the letter ‘e’.

22. Retrieve all customers with a balance greater than or equal to 500, and order them by balance in descending order.
SELECT *
FROM Accounts
WHERE Balance >= 500
ORDER BY Balance DESC;
Explanation
Retrieves all columns from the "Accounts" table where the balance is greater than or equal to 500 and then sorts the results in descending order based on the Balance amount.

23. Retrieve the first five customers by account creation date.
Oracle
SELECT * FROM Customers
ORDER BY AccountCreationDate
FETCH FIRST 5 ROWS ONLY;
Explanation
This query grabs all columns from the Customers table and sorts the data by AccountCreationDate from oldest to newest. It then pulls just the first 5 rows from the sorted data. This allows you to see the details of the 5 customers who opened their accounts first in the database.
MySQL and PostgreSQL
SELECT * FROM Customers
ORDER BY AccountCreationDate LIMIT 5;
Explanation
Pulls all columns from the Customers table and puts the rows in order from earliest to latest based on the AccountCreationDate column. The LIMIT 5 clause limits the output to just the first 5 rows of the ordered data. This gives a fast look at the oldest account creation dates in the Customers table helping to spot the longest-standing accounts in the database.
SQL Server
SELECT TOP 5 * FROM Customers
ORDER BY AccountCreationDate;
Explanation
This query reads top 5 records from the Customers table and sorts it in ascending order according to AccountCreationDate column. This helps to fastly find the account creation start dates in the table without looking at all the data.
Restricting the output to only the first five rows allows users to focus on older data to find older audit dates. This query is useful to allow retrieving and displaying old account creation dates from the Customers table.

24. Retrieve transactions with an amount between 100 and 1000 and sort them by date.
SELECT *
FROM Transactions
WHERE Amount BETWEEN 100 AND 1000
ORDER BY tDate;
Query executes on all datbases.
Explanation
This query is often used in database to extract and organize customer data based on specific criteria. The query retrieve data from a database table called Transactions. It precisely selects all columns and rows from the Transactions table with the Amount column values falls between 100 to 1000. The retrieved data will be sorted by the Date column in ascending order.

25. Retrieve all customers whose names start with the letter 'A' and order them by name.
SELECT *
FROM Customers
WHERE Name LIKE 'A%'
ORDER BY Name;
Explanation
The query fetch customers whose names begins with the alphabet ‘A’. The LIKE patten matching operator % wildcard is used to match any character followed by an 'A'. The results are then sorted alphabetically by name.

Previous Topic:-->>Retrive data Using Select || Next topic:-->>Single Row Function SQL